﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.OracleClient;

/*
 * Author:Conis
 * Date:
 * Description:对数据库的访问
 */

namespace Conis.Squirrel.SqlBuilder.Oracle
{
    /// <summary>
    /// 对数据库的访问
    /// </summary>
    public class DataAccess : BaseDataAccess
    {

        public DataAccess(BaseSqlFactory sqlFactory, string tableName, string primaryKey) : 
            base(sqlFactory, tableName, primaryKey) { }
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        protected override IDataParameter GetParameter(string key, object value)
        {
            return new OracleParameter(key, value);
        }

        /// <summary>
        /// 获取单页数据
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="condition"></param>
        /// <param name="fields"></param>
        /// <param name="orderBy"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected override DataTable GetSinglePage(Pagination pagination, string condition, string fields, string orderBy, params IDataParameter[] parameters)
        {
            string sql;
            //第一页，使用Top即可
            if (pagination.PageIndex == 1)
            {
                sql = string.Format("SELECT TOP {0} {1} FROM {2}{3}{4}",
                    pagination.PageSize, fields, this.TableName, condition, orderBy);
            }
            else
            {
                int startIndex = PaginationHelper.StartIndex(pagination.PageIndex, pagination.PageSize);
                int endIndex = PaginationHelper.EndIndex(startIndex, pagination.PageSize);
                sql = "SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT * FROM Article) A WHERE ROWNUM <= 40 ORDER BY ClientSiteID ASC ) WHERE RN BETWEEN 1 AND 10";
                //sql = "SELECT {0} FROM ( SELECT ROW_NUMBER() OVER(ORDER BY {1}) AS RowNum,{0} FROM {2}{3}) T WHERE T.RowNum BETWEEN {4} and {5}{6}";
                //sql = string.Format(sql, fields, this.PrimaryKey, this.TableName, condition, startIndex, endIndex, orderBy);

            }
            return this.SqlFactory.ExecuteSql.GetDataTable(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 插入一条数据
        /// </summary>
        /// <param name="nameValue"></param>
        /// <param name="ignoreNull"></param>
        /// <param name="ignorePrimaryKey"></param>
        /// <returns></returns>
        public override object Insert(Hashtable nameValue, bool ignoreNull, bool ignorePrimaryKey)
        {
            IDataParameter[] parameters;
            string sql = this.GetInsertSql(nameValue, ignoreNull, ignorePrimaryKey, out parameters);
            //sql += "; SELECT @@IDENTITY;";
            object result = this.SqlFactory.ExecuteSql.ExecuteScalar(sql, CommandType.Text, parameters);
            if (ignorePrimaryKey)
            {
                return result;
            }
            else
            {
                return nameValue[this.PrimaryKey];
            }
        }

        /// <summary>
        /// 获取插入的SQL语句
        /// </summary>
        /// <param name="nameValue">字段名与值的Hashtable</param>
        /// <param name="ignoreNull">是否忽略空值</param>
        /// <param name="ignorePrimaryKey">是否忽略主键</param>
        /// <param name="parameters">参数</param>
        /// <returns>返回插入数据的SQL语句</returns>
        protected override string GetInsertSql(Hashtable nameValue, bool ignoreNull, bool ignorePrimaryKey, out IDataParameter[] parameters)
        {
            StringBuilder sbFields = new StringBuilder();           //字段名收集器
            StringBuilder sbValues = new StringBuilder();           //值收集器
            List<IDataParameter> list = new List<IDataParameter>();
            int index = 0;
            string guid;
            foreach (DictionaryEntry item in nameValue)
            {
                //如果值为Null，且忽略空值，则跳过；如果忽略主键，且当前键为主键，则跳过
                if ((item.Value == null && ignoreNull) ||
                    (ignorePrimaryKey && item.Key.ToString() == this.PrimaryKey))
                {
                    continue;
                }
                //不是第一个，需要加上逗号
                if (index > 0)
                {
                    sbFields.Append(",");
                    sbValues.Append(",");
                }

                //值为Null但不忽略
                if (item.Value == null)
                {
                    sbFields.Append(item.Key);
                    sbValues.Append("NULL");
                }
                else
                {
                    guid = "G";
                    guid += Guid.NewGuid().ToString("N");
                    guid = guid.Substring(0, 25);
                    sbFields.Append(item.Key);
                    sbValues.Append(":").Append(guid);
                    list.Add(this.GetParameter(guid, item.Value));
                }
                index++;
            }
            string sql = "INSERT INTO {0}({1}) VALUES ({2})";
            parameters = list.ToArray();
            return string.Format(sql, this.TableName, sbFields.ToString(), sbValues.ToString());
        }

        /// <summary>
        /// 根据条件更新记录
        /// </summary>
        /// <param name="condition">查询条件</param>
        /// <param name="nameValue">字段对应的值</param>
        /// <param name="ignoreNull">是否忽略空值</param>
        public override void Update(BaseCondition condition, Hashtable nameValue, bool ignoreNull)
        {
            if (nameValue.Count == 0)
            {
                return;
            }
            StringBuilder sb = new StringBuilder("UPDATE ").Append(this.TableName).Append(" SET ");
            List<IDataParameter> paramArray = new List<IDataParameter>();
            int index = 0;
            string guid;
            foreach (DictionaryEntry item in nameValue)
            {
                //如果值为Null，且忽略空值，则跳过当次更新
                if (item.Value == null && ignoreNull)
                {
                    continue;
                }
                //不是第一个，需要加上逗号
                if (index != 0)
                {
                    sb.Append(",");
                }

                //值为Null但不忽略
                if (item.Value == null)
                {
                    sb.Append(item.Key).Append(" = NULL");
                }
                else
                {
                    guid = "G";
                    guid += Guid.NewGuid().ToString("N");
                    guid = guid.Substring(0, 25);
                    sb.Append(item.Key.ToString()).Append(" = :").Append(guid);
                    paramArray.Add(this.GetParameter(guid, item.Value));
                }
                index++;
            }

            string conditionStr = string.Empty;
            if (condition != null)
            {
                conditionStr = condition.ToString(true, true);
                //paramArray.AddRange(.ToArray<SQLiteParameter>());
                foreach (IDataParameter item in condition.Parameters())
                {
                    paramArray.Add(item);
                }
            }
            string sql = sb.ToString() + conditionStr;
            this.SqlFactory.ExecuteSql.ExecuteNonQuery(sql, CommandType.Text, paramArray.ToArray());
        }
    }
}
